package com.mysql.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.my.crawler.MyConfig;
import com.plf.model.WeiBoMain;

public class WeiBoContentDBUtils {

	/**** 微博内容数据表操作 ***/
	final static String SQL_URL = "jdbc:mysql://localhost:3306/"
			+ MyConfig.WeiboContentDBName;
	final static String USER_NAME = "root";
	final static String PASSWORD = "123456789";
	final static String DRIVER_URL = "com.mysql.jdbc.Driver";

	public static Connection conn = null;

	public static void createWeiBoContentTable(String TableName) {
		String biaoming = TableName;
		if (conn == null)
			conn = getConnection();
		String sqlCreate = "CREATE TABLE IF NOT EXISTS "
				+ TableName
				+ " ("
				+ "Id int(11) NOT NULL AUTO_INCREMENT,"
				+ "WeiBoId varchar(20) not null unique,MainText longtext,Time datetime,NewCommentUrl varchar(100),OldCommentUrl varchar(100),RelayFromUser varchar(50),RelayReason longtext"
				+ ",PRIMARY KEY (Id,WeiBoId)"
				+ ") ENGINE=MyISAM DEFAULT CHARSET=utf8";
		try {
			Statement stCreate = (Statement) conn.prepareStatement(sqlCreate);
			if (!isExsit(biaoming, conn)) {
				int count = stCreate.executeUpdate(sqlCreate);
				System.out.println("Success create table:" + biaoming + " "
						+ count);
			} else
				System.out.println("Failed create table:" + biaoming + ","
						+ biaoming + " is Exsited");
			stCreate.close();
			stCreate.cancel();
			stCreate = null;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Failed create table:" + biaoming);
		}
	}

	public static boolean insertWeiBoContentTable(String tableName,
			WeiBoMain wbm) {
		if (conn == null) {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {
				e.printStackTrace();
			}
			conn = getConnection(); // Get the connection with database
			System.out.println("reconnectDB");
		}
		try {
			String sql = "insert into " + tableName
					+ "(WeiBoId,MainText,Time,NewCommentUrl,OldCommentUrl,RelayFromUser,RelayReason)value('"
					+ wbm.getId() + "','" + wbm.getText() + "','"
					+ wbm.getTime() + "','" + wbm.getNewCommentUrl() + "','"
					+ wbm.getOldCommentUrl()+"','"+wbm.getRelayFromUser()+"','"+wbm.getRelayReason() + "')";

			PreparedStatement ps = (PreparedStatement) conn
					.prepareStatement(sql);
			ps.execute();
			ps.close();
			ps.cancel();
			ps = null;

			System.out.println("success insert one data");
			return true;
		} catch (SQLException e) {
			System.out.println("Fail to insert===>" + e.getMessage());

		}
		return false;
	}

	public static List<WeiBoMain> getWeiBoContent(String tablename,
			String begintime, String endtime) {
		List<WeiBoMain> wbms = new ArrayList<WeiBoMain>();
		try {
			String sql = "select * from " + tablename + " where Time>='"
					+ begintime + "'and Time<='" + endtime + "'";
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sql);

			while (rs.next()) {
				WeiBoMain wbm = new WeiBoMain();
				wbm.setId(rs.getString("WeiBoId"));
				wbm.setText(rs.getString("MainText"));
				wbm.setTime(rs.getString("Time"));
				wbm.setNewCommentUrl(rs.getString("NewCommentUrl"));
				wbm.setOldCommentUrl(rs.getString("OldCommentUrl"));
				wbm.setRelayFromUser(rs.getString("RelayFromUser"));
				wbm.setRelayReason(rs.getString("RelayReason"));
				wbms.add(wbm);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wbms;
	}

	public static Connection getConnection() {
		Connection con = null;
		try {
			System.out
					.println("=================new a sql conn link============");
			Class.forName(DRIVER_URL);
			con = DriverManager.getConnection(SQL_URL, USER_NAME, PASSWORD);
			if (conn != null) {
				System.out.println("Success to connect the database");
			}
		} catch (SQLException e) {
			System.out.println("Fail to connect the database" + e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}

		return con;
	}

	private static boolean isExsit(String tablename, Connection conn) {
		if (conn != null)
			try {
				DatabaseMetaData DBmd = (DatabaseMetaData) conn.getMetaData();
				ResultSet rs = DBmd.getTables(null, null, tablename, null);
				if (rs.next()) {
					DBmd = null;
					rs = null;
					return true;
				}

				else {
					DBmd = null;
					rs = null;
					return false;
				}
			} catch (SQLException e) {
				e.printStackTrace();
				System.out
						.println("From isExist,Fail to create dbmd,search tablename failed");
			}
		return false;
	}
}
